package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.domain.Category;
import com.LostAndFound.utils.DataSourceUtils;

public class CategoryListDao {

	public List<Category> showAllCategoryName() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category";
		List<Category> categoryList = runner.query(sql, new BeanListHandler<Category>(Category.class));
		return categoryList;
	}

	// 分页
	public int getTotalCategoryListForEasyUI() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from category";
		Long total = (Long) runner.query(sql, new ScalarHandler());
		return total.intValue();
	}
	public List<Category> getCategoryListForEasyUI(int startIndex, int pageSize) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from category limit ?,?";
		return runner.query(sql, new BeanListHandler<Category>(Category.class),startIndex,pageSize);
	}
	// alter the cid in article, 在删除category中某一类时，置article中对应的cid为“其他”的cid
	public void alterArticleCidByCid(String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update article set cid='999999999' where cid=?";
		runner.update(sql, cid);		
	}
	// delete category by cid
	public void deleteCategoryByCid(String cid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from category where cid = ?";
		runner.update(sql, cid);		
	}

	public void handleEditCategoryEasyUI(Category category) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update category set cname = ? where cid = ?";
		runner.update(sql,category.getCname(),category.getCid());
	}

	public void handleAddCategoryEasyUI(Category category) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert category(cname) values(?)";
		runner.update(sql,category.getCname());
	}
}
